> - Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- The Constant Rate Projections category, comprising the Constant Rate, Constant Rate Market and Constant Rate Revenue families
- The Stepped Rate Projections category , comprising the Stepped Rate From, Stepped Rate To, Stepped Rate Market and Stepped Rate Revenue families.
Rate Projections Functions Walkthrough | |
| Discussion of the logic of the Rate Projections functions | |
| The central core of the projections section of the library revolves around two categories of functions: Constant Rate The Constant Rate family is about projecting a single constant rate. It does not have to be a flat rate however, it can be a growing rate. Hence, as well as Con you also get AnnGrow and ConGrow, functions that take a rate and grow it at regular intervals. You might use these functions for growing a production rate, sales, etc. A very common function that is a simplification of ConGrow is AnnGrow, which does a constant rate that grows on an annual basis, in other words holding a rate constant for a year and then changing it for the next year. Then there is RePhaseCon, which is really two Cons, which together take an amount from one place in the cash flow and put in somewhere else - in this way it rephases an amount. What"s noteworthy about this function is that it does not create or destroy cash flow - the total amount is shifted and the rate at the new location altered accordingly to keep the total amount of cash in balance. There are many ways of growing a rate. ConGrow, for example, uses a set of GrowthRates and GrowthDates. Another way is to refer the rate every so often to a background market rate. This is what the functions in the Constant Rate Market family do. And because of these two ways of specifying a market forecast, there are two main functions here, ConGrowMkt and ConFcstMkt. This gives a level of indirection to the determination of the rate, which can be determined with respect to a forecast. These functions are very suitable for Real Estate work, because you can set a starting rent and have the rent refer to a market forecast. The result can be multiplied by area to give the rent. One of the most common tasks is to take a rate and multiply it by a prevailing price to get Revenue. This is what the functions of the Constant Rate Revenue family do. All these functions actually do is take a function from the Constant Rate Market and multiply by an annual rate. However, they make revenue projection much easier because if you were to have price and rate as separate lines in your model and multiplying together, then, depending on how you have done it, there are no guarantees that if you run the model on a different timebase you will get consistent results. With the functions in the Revenue families you can have confidence that a different time base will be taken in the functions stride. Stepped Rate Moving on from a constant rate to a stepped rate, stepped rate projections come in two main forms, depending on in the input data. If you want to specify rates from certain dates, use the Stepped Rate From family. For specifying rates up to certain dates, use the Stepped Rate To family. The main function in the Stepped Rate To family is clearly TStep, which simply projects a stepped rate using a set of ToDates. Moving on from there, TStepGrow does a stepped rate where you don"t have to provide all the steps - the function will create them from GrowthRates. What you have to provide is an InitialAnnRate and of course the GrowthRates. A similar function is TStepFcst, where instead of GrowthRates a range of FcstVals is used. This function is quite suited to Real Estate work because future rents are generated of a forecast. TStepGrown is like a straight TStep except each rate is grown - so it is like forecasting rates in real terms and having GrowthRates inflate them. The Stepped Rate From family is similar to the Stepped Rate To family, so all that has been said previously applies here too. The only difference is that the steps are specified in terms of FromDates instead of ToDates. The Stepped Rate Market family deals with rates that refer at times to a market rate. It actually works a little differently to the Constant Rate Market family, where a constant rate was periodically reviewed to a market rate. What the Stepped Rate Market family does is look for gaps in the steps you provided (i.e. you provided a date but not a rate), and where a gap exists (i.e. a blank or zero rate), it will supply a rate read from it"s market forecast. Finally, the Stepped Rate Revenue family forecasts revenue based on multiplying a price by a rate. See what was said above for the Constant Rate Revenue family, because the same applies here. The thing to remember is that the rate part is just a TStep or FStep and that in, say FStepRevGrow the GrowthRates apply to the MktPrice not the rate. What the function is doing, by the way, is a series of ConRevGrows for each step. In this family there are 4 key functions, 2 for the using FromDates and ToDates to specify the rate changes and 2 for the difference between using GrowthRates/MktPrice and FcstVals to describe the market price forecast. The same kinds of idea in these projections also apply in other areas. Throughout the library wherever a market price forecast is required, you"ll see we use both the GrowthRates and FcstVals approach, to give rise to a ...Fcst... as well as a ...Grow... function. The specialist categories of Real Estate and Petroleum Economics also use these techniques. |